#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use utf8;
use Encode;

# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});

my $fichier=$ARGV[0];
my $exp=encode_utf8($ARGV[1]);
my $gene;
my $date_test;

if($fichier=~/\w+ (\w+)-(\d\d-\d\d-\d\d\d\d)/){
	$gene=$1;
	$date_test=$2;
	$date_test=~s/-/\//g;
}

# ************************************* Création type_test ************************************* #
my $id_type_test;
$id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label LIKE '%Séquencage%'");

if(not $id_type_test){
	$dbh->do("INSERT INTO Type_test(label) VALUES ('Séquencage')"); 
	$id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label LIKE '%Séquencage%'");
}

# ************************************* Création test ************************************* #
my $id_test;
$id_test=$dbh->selectrow_array("SELECT id_test FROM test WHERE date_test=(E'$date_test') AND experimentateur='$exp' AND id_type_test=$id_type_test");

if(not $id_test){
	$dbh->do("INSERT INTO Test(date_test,id_type_test,experimentateur) VALUES ('$date_test',$id_type_test,'$exp')"); 
	$id_test=$dbh->selectrow_array("SELECT id_test FROM test WHERE date_test=(E'$date_test') AND id_type_test=$id_type_test AND experimentateur='$exp'");
}

my $worksheet;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

foreach($workbook->worksheets()){
    $worksheet=$_;
}
    
my ($row_min,$row_max) = $worksheet->row_range();
my ($col_min, $col_max) = $worksheet->col_range();
	
for my $col ($col_min .. $col_max){ #parcours colonnes
	my $cont2=$worksheet->get_cell(0,$col);
	my $sexe;
	my $code;
	my $histo;
	if($cont2){
		my $animal=$cont2->value(); #infos animal
		if($animal=~/^([FM]{1})(\d{1,4})-(\d{1,4})$/){
			$sexe=$1;
			$code=$2;
			$histo=$3;
		}
	}
	if($code){
		# ************************************* Création/mise à jour animal ************************************* #
		my $prep_anim=$dbh->prepare("SELECT id_animal,sexe,code_animal,code_histologique FROM Animal WHERE code_animal=(E'$code')");
		my $id_anim;
		my $sexe2;
		my $code2;
		my $histo2;
		
		$prep_anim->execute();
		while( my $ref=$prep_anim->fetchrow_hashref()){
			$id_anim=$ref->{'id_animal'};
			$sexe2=$ref->{'sexe'};
			$code2=$ref->{'code_animal'};
			$histo2=$ref->{'code_histologique'};
		}
		
		if(not $id_anim){
			$dbh->do("INSERT INTO Animal(code_animal, code_histologique, sexe) VALUES ('$code',$histo,'$sexe')");
			$id_anim=$dbh->selectrow_array("SELECT id_animal FROM animal WHERE code_animal=(E'$code') AND code_histologique=$histo");
		}
		elsif($id_anim and not $code2){
			$dbh->do("UPDATE Animal SET code_animal=(E'$code') WHERE id_animal=$id_anim");
		}
		elsif($id_anim and not $sexe2){
			$dbh->do("UPDATE Animal SET sexe=(E'$sexe') WHERE id_animal=$id_anim");
		}
		elsif($id_anim and not $histo2){
			$dbh->do("UPDATE Animal SET code_histologique=$histo WHERE id_animal=$id_anim");
		}

		# ************************************* Création resultat_test ************************************* #
		my $id_res2;
		$id_res2=$dbh->selectrow_array("SELECT id_res from resultat_test WHERE id_test=$id_test and id_animal=$id_anim");
		
		if(not $id_res2){
			$dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($id_test, $id_anim)");
			$id_res2=$dbh->selectrow_array("SELECT id_res from resultat_test WHERE id_test=$id_test and id_animal=$id_anim");
		}
		
		# ************************************* Création sequencage_ADN ************************************* #
		my $id_res3;
		$id_res3=$dbh->selectrow_array("SELECT id_res from sequencage_ADN WHERE id_res=$id_res2");
		
		if(not $id_res3){
			$dbh->do("INSERT INTO Sequencage_ADN(id_res) VALUES($id_res2)");
			$id_res3=$dbh->selectrow_array("SELECT id_res from sequencage_ADN WHERE id_res=$id_res2");
		}
		
		# ************************************* Création gene ************************************* #
		my $id_res4;
		$id_res4=$dbh->selectrow_array("SELECT id_res from gene WHERE id_res=$id_res3 AND nom_gene='$gene'");
		
		if(not $id_res4){
			$dbh->do("INSERT INTO gene(id_res,nom_gene) VALUES($id_res3,'$gene')");
			$id_res4=$dbh->selectrow_array("SELECT id_res from gene WHERE id_res=$id_res3 AND nom_gene='$gene'");
		}

		for my $row (1 .. $row_max){ #parcours lignes
			if($col>3){
				my $cont=$worksheet->get_cell($row,$col);
				if($cont and ($cont->value() eq 0 or $cont->value() eq 1 or $cont->value() eq 2)){
					my $valeur=$cont->value(); #valeur mutation/snp
					
					my $cont3=$worksheet->get_cell($row,0);
					my $exon=$cont3->value(); #nom exon
					
					my $cont4=$worksheet->get_cell($row,1);
					my $type=$cont4->value(); #type mutation
					
					my $cont5=$worksheet->get_cell($row,3);
					my $mut=$cont5->value(); #mutation/snp
					
					my $cont6=$worksheet->get_cell($row,2);
					my $temp=$cont6->value();
					my $localisation;
					my $type_variation;
					if($temp=~/\(([+-]{1} ?\d{1,3})\)\w+ ?([A-Za-z]+)/){ #ex: (-90)del GAACTGTTTA
						$localisation="$exon, $1";
						$type_variation=$2;
					}
					elsif($temp=~/([+-]{1}\d{1,3}) ?(\w{3}) {1}([A-Za-z]+)/){ #ex: +90Del T
						$localisation="$exon, $1";
						$type_variation="$2($3)";
					}
					elsif($temp=~/([+-]{1}\d{1,3}) {1}\(?([A-Za-z\/-]+)\)?/){ #ex: +57 A/T
						$localisation="$exon, $1";
						$type_variation="$2";
					}
					elsif($temp=~/([pP]\. ?\w{3}\d{1,3}\w{0,3}) {1}\(([A-Za-z -]*)\)/){ #ex: p. Asp227Gly (GAT-GGT) ou p. Tyr567 (TAC-TAT)
						$localisation="$1";
						$type_variation=$2;				
					}
					elsif($temp=~/([Pp]\.\d{3}-\d{3}) ?(\w{3}) ?([A-Za-z\/]+)/){ #ex: p.641-642 Ins AAGACG
						$localisation=$1;
						$type_variation="$2($3)";	
					}
					
					elsif($temp=~/([+-]{1}\d{1,3}) ?[A-Za-z() ]\/([A-Za-z() ])/){ #ex: +63 Del (GA)/Ins(CAGAAA)
						$localisation=$1;
						$type_variation="$2";	
					}
					# ************************************* Création snp ************************************* #
					if($localisation and $type_variation){
						if($mut eq 'SNP'){
							my $id_snp;
							$id_snp=$dbh->selectrow_array("SELECT id_res from SNP WHERE id_res=$id_res4 AND nom_gene='$gene' AND localisation='$localisation'");
							
							if(not $id_snp){
								$dbh->do("INSERT INTO SNP(id_res,nom_gene,type_variation,localisation,valeur_res) VALUES($id_res4,'$gene','$type_variation','$localisation',$valeur)");
							}
						}
						elsif($mut eq 'mutation'){
							my $id_mut;
							$id_mut=$dbh->selectrow_array("SELECT id_res from Mutation WHERE id_res=$id_res4 AND nom_gene='$gene' AND localisation='$localisation'");
							
							if(not $id_mut){
								my $temp=$dbh->quote($type);
								my $new=encode_utf8($temp);
								$dbh->do("INSERT INTO Mutation(id_res,nom_gene,type_variation,type_mutation,localisation,valeur_res) VALUES($id_res4,'$gene','$type_variation',$new,'$localisation',$valeur)");
							}
						}
					}
				}
			}
		}
	}
}
				
$dbh->disconnect();

